package dao;

import interfacePack.ITableObserver;



import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;


import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import context.ExcelConnection;
import exception.ViewException;

public class Excel07DAO {

public static ArrayList<ITableObserver> observers = new ArrayList<ITableObserver>();
	
	public static void register(ITableObserver observer){
		observers.add(observer);
	}
	
	//检查excel文件是否为标准形式：只检查是否包含9列以及相应内容
	public static boolean checkHead(XSSFRow head) {
		String[] titleCN = {"流水号","交易日期","交易时间","发起机构","接受机构","卡号","交易金额","交易状态","交易码"};
		String[] titleEN = {"tradeNO","tradeDate","tradeTime","fromParty","toParty","cardID","amount","tradeState","tradeType"};
		for(int i = 0; i < titleCN.length; i++) {
			if(!head.getCell(i).toString().equals(titleCN[i]) && !head.getCell(i).toString().equals(titleEN[i]))
				return false;
			if(head.getCell(i).toString().equals(titleCN[i])) {
				head.getCell(i).setCellValue(titleEN[i]);
			}
		}
		return true;
	}

	public static void getExcelRecord(String tableName) throws ViewException{

		ExcelConnection connection = ExcelConnection.newInstance();

		XSSFWorkbook wb = null;

		XSSFSheet contents = null;

		// 打开.xlsx文件
		try {
			
			wb = new XSSFWorkbook(connection.getAddr());

		} catch (FileNotFoundException e) {

			throw new ViewException("找不到文件");

		} catch (IOException e) {

			e.printStackTrace();
			throw new ViewException("文件读取出错");


		}

		// 取第一个sheet
		contents = wb.getSheetAt(0);

		int minColIdx, maxColIdx, maxRowIdx;

		maxRowIdx = contents.getLastRowNum();

		XSSFRow xlrow = contents.getRow(0);
		//check head:检查导入表是否为标准格式
		if(!checkHead(xlrow)) {
			throw new ViewException("文件不是标准格式");
		}

		// -----------------------建立MySQL连接-------------------------
		try {

			// 建立一个mysql的driver的实例，并将其注册到DriversManager
			Class.forName("com.mysql.jdbc.Driver");

		} catch (ClassNotFoundException e) {

			e.printStackTrace();
			throw new ViewException("连接mysql出错，请添加mysql jdbc driver");
		}

		String name = "root";

		String password = "root123";

		String url = "jdbc:mysql://localhost:3306/easipaybank";

		try {

			// 建立连接
			Connection con = DriverManager.getConnection(url, name, password);

			Statement stmt = con
					.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
							ResultSet.CONCUR_UPDATABLE);

			try {

				/*
				 * 建表 stmt.execute("create table ORGDATAS( " +
				 * "USERID VARCHAR(24) NOT NULL PRIMARY KEY," +
				 * " USERNAME VARCHAR(24), SEX TINYINT, " +
				 * "PASSWORD VARCHAR(64), USERTYPE TINYINT, " +
				 * "FREEAUTHEN TINYINT, CERTIFICATETYPE TINYINT, " +
				 * "CERTIFICATENO VARCHAR(24), EDUCATION TINYINT, " +
				 * "POSTCODE VARCHAR(10), ADDRESS VARCHAR(128), " +
				 * "PHONENO VARCHAR(24), BIRTHDAY DATE, EMAIL VARCHAR(64) );");
				 */
				XSSFCell cel, refcell;

				XSSFRow ferr = contents.getRow(1);

				String ctbsql = "create table " + tableName + " (";

				for (int i = xlrow.getFirstCellNum(); i < xlrow
						.getLastCellNum(); i++) {

					cel = xlrow.getCell(i);

					refcell = ferr.getCell(i);


					if (refcell == null) {

						ctbsql = ctbsql.concat(cel.getStringCellValue()
								+ " VARCHAR(64)");

					} else {
						
						if(!(cel.getStringCellValue().equals("amount") || cel.getStringCellValue().equals("交易金额"))) {
							ctbsql = ctbsql.concat(cel.getStringCellValue()
									+ " VARCHAR(64)");
						}
						else
							ctbsql = ctbsql.concat(cel.getStringCellValue()
									+ " decimal(8,2)");

//						switch (refcell.getCellType()) {
//
//						case Cell.CELL_TYPE_FORMULA:
//
//						case Cell.CELL_TYPE_STRING:
//							ctbsql = ctbsql.concat(cel.getStringCellValue()
//									+ " VARCHAR(64)");
//							break;
//
//						case Cell.CELL_TYPE_NUMERIC:
//							ctbsql = ctbsql.concat(cel.getStringCellValue()
//									+ " INT");
//							break;
//
//						case Cell.CELL_TYPE_BOOLEAN:
//							ctbsql = ctbsql.concat(cel.getStringCellValue()
//									+ " TINYINT");
//							break;
//
//						default:
//							ctbsql = ctbsql.concat(cel.getStringCellValue()
//									+ " VARCHAR(64)");
//
//						}

					}

					if (i < xlrow.getLastCellNum() - 1) {

						if (i == 0) {

							ctbsql = ctbsql.concat(" NOT NULL PRIMARY KEY");

						}

						ctbsql = ctbsql.concat(", ");

					} else {

						ctbsql = ctbsql.concat(");");

					}

				}

				stmt.execute(ctbsql);
				// 跟据前两行的内容来建表

			} catch (SQLException e) {

				e.printStackTrace();
				throw new ViewException(e.toString());

			}

			//创建视图
			CreateView.createViews(tableName);
			
			ResultSet rs = stmt.executeQuery("select * from NEWPAYRECORD");
			

			// 设定每列的最小最大索引
			minColIdx = xlrow.getFirstCellNum();

			maxColIdx = xlrow.getLastCellNum();

			int cnt = 0;

			boolean infirstrow = true;

			// for each式遍历整个表
			for (Row row : contents) {
				
				if (infirstrow) {

					infirstrow = false;

					continue;

				}
				rs.moveToInsertRow();


				ArrayList<String> cells = new ArrayList<String>();
				
				for (Cell cell : row) {

					if (cell == null) {

						continue;

					}
					cells.add(cell.toString());
					
					switch (cell.getCellType()) {

					case Cell.CELL_TYPE_FORMULA:

					case Cell.CELL_TYPE_STRING:
						rs.updateString(cell.getColumnIndex() + 1, cell
								.getStringCellValue());
						break;

					case Cell.CELL_TYPE_NUMERIC:
						rs.updateInt(cell.getColumnIndex() + 1, (int) cell
								.getNumericCellValue());
						break;

					case Cell.CELL_TYPE_BOOLEAN:
						rs.updateShort(cell.getColumnIndex() + 1, (short) cell
								.getNumericCellValue());
						break;

					default:
						rs.updateString(cell.getColumnIndex() + 1, cell
								.getStringCellValue());

					}

				}

				for(ITableObserver observer : observers){
					observer.update(cells);
				}
				
				rs.insertRow();

			}
			for(ITableObserver observer : observers){
				observer.updateFinished();
			}


		} catch (SQLException e) {

			e.printStackTrace();
			throw new ViewException(e.toString());


//			while (e != null) {
//
//
//				e = e.getNextException();
//
//				e.printStackTrace();
//
//
//			}

		} catch (IllegalStateException ie) {

			ie.printStackTrace();
			throw new ViewException(ie.toString());

		}

	}
	
}
